using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Fieldbook.Entities;
using Fieldbook.Common;
namespace Fieldbook.DAL
{
	public static class FieldDAO
	{
		#region Insert
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
            string insertSQL = @"INSERT INTO dbo.Field (CATASTER, OWNERID, MEASUREUNITID, AREA, FIELDSTATEID, FIELDMAPID, FIELDMAPX, FIELDMAPY) 
                                                VALUES (@CATASTER, @OWNERID, @MEASUREUNITID, @AREA, @FIELDSTATEID, @FIELDMAPID, @FIELDMAPX, @FIELDMAPY) 
                                 SET @FIELDID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@FIELDID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@CATASTER", DbType.String);
            db.AddInParameter(insertCommand, "@OWNERID", DbType.Int64);
            db.AddInParameter(insertCommand, "@MEASUREUNITID", DbType.Int32);
            db.AddInParameter(insertCommand, "@AREA", DbType.Decimal);
            db.AddInParameter(insertCommand, "@FIELDSTATEID", DbType.Byte);
            db.AddInParameter(insertCommand, "@FIELDMAPID", DbType.Int32);
            db.AddInParameter(insertCommand, "@FIELDMAPX", DbType.Int16);
            db.AddInParameter(insertCommand, "@FIELDMAPY", DbType.Int16);
            return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Field entity)
		{
			db.SetParameterValue(cmd, "@CATASTER", entity.Cataster);
			db.SetParameterValue(cmd, "@OWNERID", entity.Ownerid);
			db.SetParameterValue(cmd, "@MEASUREUNITID", entity.Measureunitid);
			db.SetParameterValue(cmd, "@AREA", entity.Area);
            db.SetParameterValue(cmd, "@FIELDSTATEID", entity.FieldStateID);
            db.SetParameterValue(cmd, "@FIELDMAPID", entity.FieldMapID);
            db.SetParameterValue(cmd, "@FIELDMAPX", entity.FieldMapX);
            db.SetParameterValue(cmd, "@FIELDMAPY", entity.FieldMapY);
		}
		public static bool InsertField(Field entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Fieldid = (long?)db.GetParameterValue(cmd, "@FIELDID");
			}
			return res;
		}
		public static bool InsertField(Field entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Fieldid = (long?)db.GetParameterValue(cmd, "@FIELDID");
			}
			return res;
		}
		public static bool InsertListField(List<Field> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Field entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Fieldid = (long?)db.GetParameterValue(cmd, "@FIELDID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListField(List<Field> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Field entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Fieldid = (long?)db.GetParameterValue(cmd, "@FIELDID");
				}
			}
			return res;
		}
		#endregion

		#region Update

		private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
            string updateSQL = "UPDATE dbo.Field SET CATASTER = @CATASTER, OWNERID = @OWNERID, MEASUREUNITID = @MEASUREUNITID, AREA = @AREA WHERE FIELDID = @FIELDID";
            DbCommand updateCommand = db.GetSqlStringCommand(updateSQL);
            db.AddInParameter(updateCommand, "@FIELDID", DbType.Int64);
            db.AddInParameter(updateCommand, "@CATASTER", DbType.String);
            db.AddInParameter(updateCommand, "@OWNERID", DbType.Int64);
            db.AddInParameter(updateCommand, "@MEASUREUNITID", DbType.Int32);
            db.AddInParameter(updateCommand, "@AREA", DbType.Decimal);
            return updateCommand;
		}
		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Field entity)
		{
			db.SetParameterValue(cmd, "@FIELDID", entity.Fieldid);
			db.SetParameterValue(cmd, "@CATASTER", entity.Cataster);
			db.SetParameterValue(cmd, "@OWNERID", entity.Ownerid);
			db.SetParameterValue(cmd, "@MEASUREUNITID", entity.Measureunitid);
			db.SetParameterValue(cmd, "@AREA", entity.Area);
		}
		public static bool UpdateField(Field entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool UpdateField(Field entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}
		public static bool UpdateListField(List<Field> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Field entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListField(List<Field> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Field entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}

        public static bool UpdateFieldState(long fieldID, Field.FieldState? fieldStateID)
        {
            string updateSQL = "UPDATE dbo.Field SET FIELDSTATEID = @FIELDSTATEID WHERE FIELDID = @FIELDID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSQL);
            db.AddInParameter(cmd, "@FIELDSTATEID", DbType.Int64, fieldID);
            db.AddInParameter(cmd, "@FIELDID", DbType.Byte, fieldStateID);
            return Convert.ToBoolean(db.ExecuteNonQuery(cmd));
        }

        public static bool UpdateFieldState(long fieldID, Field.FieldState? fieldStateID, DbTransaction transactionObj, SqlDatabase db)
        {
            string updateSQL = "UPDATE dbo.Field SET FIELDSTATEID = @FIELDSTATEID WHERE FIELDID = @FIELDID";
            DbCommand cmd = db.GetSqlStringCommand(updateSQL);
            db.AddInParameter(cmd, "@FIELDSTATEID", DbType.Int64, fieldID);
            db.AddInParameter(cmd, "@FIELDID", DbType.Byte, fieldStateID);
            return Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
        }

		#endregion

		#region Save
		public static bool SaveField(Field entity)
		{
			bool res = true;
			if (entity.Fieldid == null )
			{
				res = InsertField(entity);
			}
			else
			{
				res = UpdateField(entity);
			}
			return res;
		}
		public static bool SaveField(Field entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Fieldid == null )
			{
				res = InsertField(entity, transactionObj, db);
			}
			else
			{
				res = UpdateField(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListField(List<Field> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Field entity = list[i];
					res = res & SaveField(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListField(List<Field> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Field entity = list[i];
				res = res & SaveField(entity, transactionObj, db);
			}
			return res;
		}
		#endregion
		#region Delete
		private static DbCommand deleteCommand = null;
		private static string deleteSQL = "DELETE FROM dbo.Field WHERE FIELDID = @FIELDID";
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
			if (deleteCommand == null)
			{
				deleteCommand = db.GetSqlStringCommand(deleteSQL);
				db.AddInParameter(deleteCommand, "@FIELDID", DbType.Int64);
			}
			return deleteCommand;
		}
		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Field entity)
		{
			db.SetParameterValue(cmd, "@FIELDID", entity.Fieldid);
		}
		public static bool DeleteField(Field entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool DeleteListField(List<Field> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Field entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		
		#endregion
		#region Select

        private static List<Field> LoadFields(SqlDatabase db, DbCommand command)
        {
            List<Field> res = new List<Field>();
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(command);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Field entity = new Field();
                        entity.Fieldid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
                        entity.Cataster = reader.IsDBNull(1) ? null : (string)reader.GetString(1);
                        entity.Ownerid = reader.IsDBNull(2) ? null : (long?)reader.GetInt64(2);
                        entity.Measureunitid = reader.GetInt32(3);
                        entity.Area = reader.GetDecimal(4);
                        entity.FieldStateID = reader.IsDBNull(5) ? null : (Field.FieldState?)reader.GetByte(5);
                        entity.FieldMapID = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
                        entity.FieldMapX = reader.IsDBNull(7) ? null : (short?)reader.GetInt16(7);
                        entity.FieldMapY = reader.IsDBNull(8) ? null : (short?)reader.GetInt16(8);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<Field> LoadHouseHoldFields(long userID, string orderBy)
        {
            string selectSQL = String.Format(@"SELECT F.FIELDID, F.CATASTER, F.OWNERID, F.MEASUREUNITID, F.AREA, F.FIELDSTATEID, F.FIELDMAPID, F.FIELDMAPX, F.FIELDMAPY
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
WHERE HH.MEMBERID = @MEMBERID {0} ", orderBy);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return LoadFields(db, cmd);
        }

        public static List<Field> LoadHouseHoldFields(long userID, string orderBy, int maximumRows, int startRowIndex)
        {
            if (String.IsNullOrEmpty(orderBy))
            {
                orderBy = "FIELDID";
            }
            int endRowIndex = startRowIndex + maximumRows - 1;
            string selectSQL = String.Format(@"WITH FIELDS AS (
SELECT ROW_NUMBER() OVER (ORDER BY {0} ASC) AS ROW, F.FIELDID, F.CATASTER, F.OWNERID, F.MEASUREUNITID, F.AREA, F.FIELDSTATEID, F.FIELDMAPID, F.FIELDMAPX, F.FIELDMAPY
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
WHERE HH.MEMBERID = @MEMBERID {0} 
)
SELECT FIELDID,CATASTER,OWNERID,MEASUREUNITID,FIELDSTATEID,FIELDMAPID,FIELDMAPX,FIELDMAPY FROM FIELDS WHERE ROW BETWEEN {1} AND {2}",
            orderBy, startRowIndex, endRowIndex);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return LoadFields(db, cmd);
        }

        public static long? CheckHouseHoldField(long userID, long fieldID)
        {
            string selectSQL = @"SELECT F.FIELDID
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
WHERE HH.MEMBERID = @MEMBERID AND F.FIELDID =  @FIELDID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            db.AddInParameter(cmd, "@FIELDID", DbType.Int64, fieldID);
            return (long?)db.ExecuteScalar(cmd);
        }

        public static List<Field> LoadMapFields(long userID, int mapID)
        {
            string selectSQL = @"SELECT F.FIELDID, F.CATASTER, F.OWNERID, F.MEASUREUNITID, F.AREA, F.FIELDSTATEID, F.FIELDMAPID, F.FIELDMAPX, F.FIELDMAPY
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
WHERE HH.MEMBERID = @MEMBERID AND F.FIELDMAPID = @FIELDMAPID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            db.AddInParameter(cmd, "@FIELDMAPID", DbType.Int32, mapID);
            return LoadFields(db, cmd);
        }

        public static List<long> GetFieldIds(string criteria)
        {
            List<long> res = new List<long>();
            string selectSql = String.Format("SELECT MeasureUnitID FROM dbo.Field ", criteria);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, selectSql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        long id = reader.GetInt64(0);
                        res.Add(id);
                    }
                    reader.Read();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;
        }
		#endregion
	}
}
